using System.Data;
using IMClient.Model.DataBase;
using Microsoft.Data.Sqlite;

namespace IMClient.Cache.Repositories
{
    public sealed class FriendCacheRepository
    {
        private static readonly FriendCacheRepository _instance = new();
        private readonly SqliteConnection _connection;

        // 私有构造函数
        private FriendCacheRepository()
        {
            var dbContext = DbContext.Instance;
            _connection = dbContext.GetConnection();
        }

        // 单例实例
        public static FriendCacheRepository Instance => _instance;

        public async Task CacheFriendAsync(string userId, FriendDataBase friend)
        {
            await EnsureConnectionOpenAsync();

            var command = _connection.CreateCommand();
            command.CommandText = @"
                INSERT INTO friend_cache (user_id, friend_id, friend_name, friend_avatar, last_update_time)
                VALUES (@userId, @friendId, @friendName, @friendAvatar, @lastUpdateTime)
                ON CONFLICT(user_id, friend_id) 
                DO UPDATE SET 
                    friend_name = @friendName,
                    friend_avatar = @friendAvatar,
                    last_update_time = @lastUpdateTime";

            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.AddWithValue("@friendId", friend.FriendId);
            command.Parameters.AddWithValue("@friendName", friend.FriendName);
            command.Parameters.AddWithValue("@friendAvatar", friend.FriendAvatar);
            command.Parameters.AddWithValue("@lastUpdateTime", friend.LastUpdateTime.ToString("yyyy-MM-dd HH:mm:ss"));

            await command.ExecuteNonQueryAsync();
        }

        public async Task CacheFriendsAsync(string userId, IEnumerable<FriendDataBase> friends)
        {
            await EnsureConnectionOpenAsync(); 

            using var transaction = await _connection.BeginTransactionAsync();
            try
            {
                foreach (var friend in friends)
                {
                    await CacheFriendAsync(userId, friend);
                }
                await transaction.CommitAsync();
            }
            catch
            {
                await transaction.RollbackAsync();
                throw;
            }
        }

        public async Task<List<FriendDataBase>> GetCachedFriendsAsync(string userId)
        {
            await EnsureConnectionOpenAsync();

            var friends = new List<FriendDataBase>();
            var command = _connection.CreateCommand();
            command.CommandText = @"
                SELECT friend_id, friend_name, friend_avatar, last_update_time 
                FROM friend_cache 
                WHERE user_id = @userId";
            command.Parameters.AddWithValue("@userId", userId);

            using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                friends.Add(new FriendDataBase
                {
                    UserId = userId,
                    FriendId = reader.GetString(0),
                    FriendName = reader.GetString(1),
                    FriendAvatar = reader.GetString(2),
                    LastUpdateTime = DateTime.Parse(reader.GetString(3))
                });
            }

            return friends;
        }

        // 确保数据库连接已打开
        private async Task EnsureConnectionOpenAsync()
        {
            if (_connection.State != ConnectionState.Open)
            {
                await _connection.OpenAsync();
            }
        }
    }
}
